-- 电影数据库创建脚本
-- MySQL版本

-- 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS MovieDB;
USE MovieDB;

-- 删除已存在的表（如果有），注意删除顺序以避免外键约束问题
DROP TABLE IF EXISTS Participation;
DROP TABLE IF EXISTS Movie;
DROP TABLE IF EXISTS Artist;

-- 创建艺人表
CREATE TABLE Artist (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    birthday DATE,
    sex CHAR(1),
    imdbnumber VARCHAR(20),
    introduction TEXT
);

-- 创建电影表
CREATE TABLE Movie (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    releasedate DATE,
    duration INT,
    language VARCHAR(50),
    introduction TEXT
);

-- 创建艺人与电影关系表
CREATE TABLE Participation (
    id INT AUTO_INCREMENT PRIMARY KEY,
    artistid INT NOT NULL,
    movieid INT NOT NULL,
    rolename VARCHAR(50),
    FOREIGN KEY (artistid) REFERENCES Artist(id) ON DELETE CASCADE,
    FOREIGN KEY (movieid) REFERENCES Movie(id) ON DELETE CASCADE,
    -- 添加唯一约束，确保一个艺人在一部电影中只有一条记录
    UNIQUE KEY unique_participation (artistid, movieid)
);

-- 添加索引以提高查询性能
CREATE INDEX idx_artist_name ON Artist(name);
CREATE INDEX idx_movie_name ON Movie(name);
CREATE INDEX idx_participation_artist ON Participation(artistid);
CREATE INDEX idx_participation_movie ON Participation(movieid);

-- SQL Server版本注释（如需使用SQL Server，取消以下注释并调整）
/*
-- SQL Server 版本
-- 创建数据库（如果需要）
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'MovieDB')
CREATE DATABASE MovieDB;
GO
USE MovieDB;
GO

-- 删除已存在的表
IF OBJECT_ID('Participation', 'U') IS NOT NULL DROP TABLE Participation;
IF OBJECT_ID('Movie', 'U') IS NOT NULL DROP TABLE Movie;
IF OBJECT_ID('Artist', 'U') IS NOT NULL DROP TABLE Artist;
GO

-- 创建艺人表
CREATE TABLE Artist (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    birthday DATE,
    sex CHAR(1),
    imdbnumber VARCHAR(20),
    introduction TEXT
);
GO

-- 创建电影表
CREATE TABLE Movie (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    releasedate DATE,
    duration INT,
    language VARCHAR(50),
    introduction TEXT
);
GO

-- 创建艺人与电影关系表
CREATE TABLE Participation (
    id INT IDENTITY(1,1) PRIMARY KEY,
    artistid INT NOT NULL,
    movieid INT NOT NULL,
    rolename VARCHAR(50),
    FOREIGN KEY (artistid) REFERENCES Artist(id) ON DELETE CASCADE,
    FOREIGN KEY (movieid) REFERENCES Movie(id) ON DELETE CASCADE,
    UNIQUE (artistid, movieid)
);
GO

-- 添加索引
CREATE INDEX idx_artist_name ON Artist(name);
CREATE INDEX idx_movie_name ON Movie(name);
CREATE INDEX idx_participation_artist ON Participation(artistid);
CREATE INDEX idx_participation_movie ON Participation(movieid);
GO
*/